package com.kcs.fxmargin.export.excel;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;

import com.kcs.common.utils.commonUtils;
import com.kcs.fxmargin.bo.ListOfForwardContractBO;
import com.kcs.fxmargin.export.model.ReportCriteria;
import com.kcs.fxmargin.report.utils.ReportExcelUtil;

public class ListOfForwardContractExcel extends BaseReportExcel{
	
	public byte[] generateExcel(List<ListOfForwardContractBO> fcList,ReportCriteria reportCriteria) throws Exception{
		byte[] result = null;
		int maxIndexCol = 14;
        if(commonUtils.isNotEmpty(fcList)){
            
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("List of FC");           
            createDataPage(wb, sheet, reportCriteria,fcList);
            createHeaderPage(wb, sheet, reportCriteria);
            createFooterPage(wb, sheet, reportCriteria);
            ReportExcelUtil.setDefaultConfigSheet(sheet, maxIndexCol-1);
            ByteArrayOutputStream byteOut = new ByteArrayOutputStream();
            try {
                wb.write(byteOut);
                byteOut.flush();
            } catch (IOException ex) {
                ex.printStackTrace();
                throw ex;
            }finally{
                byteOut.close();
            }
            result = byteOut.toByteArray();
        }
               
		return result;
   }

	
   private void createDataPage(HSSFWorkbook wb , HSSFSheet sheet,ReportCriteria reportCriteria,List<ListOfForwardContractBO> fcList){
	   
	    HSSFRow row   = null;
	    HSSFCell cell = null;
	    if(!reportCriteria.getHeaderTable().isEmpty()){
	    	//HSSFCellStyle headerStyle = ReportExcelUtil.getDefaultCellHeaderStyle(wb, ReportExcelUtil.BG_COLOR_WHITE, ReportExcelUtil.getFontHeader(wb, HSSFColor.BLACK.index));
	    	HSSFCellStyle headerCriteriaStyle = ReportExcelUtil.getDefaultCellCriteriaStyle(wb, false, false);
	    	row = createNewRow(sheet);
	    	cell = createNewCell(row);
			cell.setCellValue("CIF No. : "+reportCriteria.getHeaderTable().get("CIF_NO"));
			cell.setCellStyle(headerCriteriaStyle);
			row = createNewRow(sheet);
			cell = createNewCell(row);
			cell.setCellValue("Customer Name : "+reportCriteria.getHeaderTable().get("CUSTOMER_NAME"));
			cell.setCellStyle(headerCriteriaStyle);
			row = createNewRow(sheet);
			cell = createNewCell(row);
			cell.setCellValue("As of Date : "+reportCriteria.getHeaderTable().get("AS_OF_DATE"));
			cell.setCellStyle(headerCriteriaStyle);
	    }
	    else{
	    	System.out.println("Report Not Found Header Data");
	    }
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 13));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 13));
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 13));
		
		row  = createNewRow(sheet);
		
		//HSSFCellStyle headerTableStyle = ReportExcelUtil.getDefaultCellHeaderStyle(wb);
		HSSFCellStyle headerTableStyle = ReportExcelUtil.getDefaultHeaderTableStyle(wb, false, false);
		//HSSFFont  font = ReportExcelUtil.getFont(wb,ReportExcelUtil.FONT_SIZE_DEFAULT,HSSFColor.BLACK.index,Font.BOLDWEIGHT_NORMAL);
		row  = createNewRow(sheet);
		cell = createNewCell(row);
		cell.setCellValue("No.");
		cell.setCellStyle(headerTableStyle);
		cell = createNewCell(row);
		cell.setCellValue("Deal No.");
		cell.setCellStyle(headerTableStyle);
		cell = createNewCell(row);
		cell.setCellValue("Days to Mat");
		cell.setCellStyle(headerTableStyle);
		cell = createNewCell(row);
		cell.setCellValue("Mat Date");
		cell.setCellStyle(headerTableStyle);
		cell = createNewCell(row);
		cell.setCellValue("Client Buy/Sell");
		cell.setCellStyle(headerTableStyle);
		cell = createNewCell(row);
		cell.setCellValue("CCY Pair");
		cell.setCellStyle(headerTableStyle);
		cell = createNewCell(row);
		cell.setCellValue("FX Rate");
		cell.setCellStyle(headerTableStyle);
		cell = createNewCell(row);
		cell.setCellValue("Amt1");
		cell.setCellStyle(headerTableStyle);
		cell = createNewCell(row);
		cell.setCellValue("Amt2");
		cell.setCellStyle(headerTableStyle);
		cell = createNewCell(row);
		cell.setCellValue("IM Required");
		cell.setCellStyle(headerTableStyle);
		cell = createNewCell(row);
		cell.setCellValue("MTM Rate");
		cell.setCellStyle(headerTableStyle);
		cell = createNewCell(row);
		cell.setCellValue("Unrealized P/L (THB)");
		cell.setCellStyle(headerTableStyle);
		cell = createNewCell(row);
		cell.setCellValue("Deal Status");
		cell.setCellStyle(headerTableStyle);
		cell = createNewCell(row);
		cell.setCellValue("Deal Type");
		cell.setCellStyle(headerTableStyle);
		
		ListOfForwardContractBO fc = null;			
		
		for(int i=0; i<fcList.size(); i++){
			fc = fcList.get(i);
			row = createNewRow(sheet);
			cell = createNewCell(row);
			cell.setCellValue(i+1);
			cell.setCellStyle(ReportExcelUtil.getDefaultCellStyle(wb,ReportExcelUtil.BG_COLOR_WHITE,ReportExcelUtil.getDefaultFont(wb), ReportExcelUtil.ALIGN_CENTER ,false,false));

			cell = createNewCell(row);
			cell.setCellValue(fc.getDealId());
			cell.setCellStyle(ReportExcelUtil.getDefaultCellStyle(wb,ReportExcelUtil.BG_COLOR_BLACK,ReportExcelUtil.getDefaultFont(wb), ReportExcelUtil.ALIGN_LEFT ,false,false));
			
			cell = createNewCell(row);
			cell.setCellValue(fc.getDayToMat());
			cell.setCellStyle(ReportExcelUtil.getDefaultCellStyle(wb,ReportExcelUtil.BG_COLOR_WHITE,ReportExcelUtil.getDefaultFont(wb), ReportExcelUtil.ALIGN_RIGHT ,false,false));
			
			cell = createNewCell(row);
			cell.setCellValue(fc.getMatDate());
			cell.setCellStyle(ReportExcelUtil.getDefaultCellStyle(wb,ReportExcelUtil.BG_COLOR_WHITE,ReportExcelUtil.getDefaultFont(wb), ReportExcelUtil.ALIGN_CENTER ,false,false));
			
			cell = createNewCell(row);
			cell.setCellValue(fc.getBuySell());
			cell.setCellStyle(ReportExcelUtil.getDefaultCellStyle(wb,ReportExcelUtil.BG_COLOR_WHITE,ReportExcelUtil.getDefaultFont(wb), ReportExcelUtil.ALIGN_LEFT ,false,false));

			cell = createNewCell(row);
			cell.setCellValue(fc.getCcyPair());
			cell.setCellStyle(ReportExcelUtil.getDefaultCellStyle(wb,ReportExcelUtil.BG_COLOR_WHITE,ReportExcelUtil.getDefaultFont(wb), ReportExcelUtil.ALIGN_LEFT ,false,false));
			
			cell = createNewCell(row);
			cell.setCellValue(fc.getFxRate());
			cell.setCellStyle(ReportExcelUtil.getDefaultCellStyle(wb,ReportExcelUtil.BG_COLOR_WHITE,ReportExcelUtil.getDefaultFont(wb), ReportExcelUtil.ALIGN_RIGHT ,false,false));
			
			cell = createNewCell(row);
			cell.setCellValue(fc.getAmt1());
			cell.setCellStyle(ReportExcelUtil.getDefaultCellStyle(wb,ReportExcelUtil.BG_COLOR_WHITE,ReportExcelUtil.getDefaultFont(wb), ReportExcelUtil.ALIGN_RIGHT ,false,false));
			
			cell = createNewCell(row);
			cell.setCellValue(fc.getAmt2());
			cell.setCellStyle(ReportExcelUtil.getDefaultCellStyle(wb,ReportExcelUtil.BG_COLOR_WHITE,ReportExcelUtil.getDefaultFont(wb), ReportExcelUtil.ALIGN_RIGHT ,false,false));
			
			cell = createNewCell(row);
			cell.setCellValue(fc.getImRequired());
			cell.setCellStyle(ReportExcelUtil.getDefaultCellStyle(wb,ReportExcelUtil.BG_COLOR_WHITE,ReportExcelUtil.getDefaultFont(wb), ReportExcelUtil.ALIGN_RIGHT ,false,false));
			
			cell = createNewCell(row);
			cell.setCellValue(fc.getMtmRate());
			cell.setCellStyle(ReportExcelUtil.getDefaultCellStyle(wb,ReportExcelUtil.BG_COLOR_WHITE,ReportExcelUtil.getDefaultFont(wb), ReportExcelUtil.ALIGN_RIGHT ,false,false));
			
			cell = createNewCell(row);
			cell.setCellValue(fc.getUnrealizedPL());
			cell.setCellStyle(ReportExcelUtil.getDefaultCellStyle(wb,ReportExcelUtil.BG_COLOR_WHITE,ReportExcelUtil.getDefaultFont(wb), ReportExcelUtil.ALIGN_RIGHT ,false,false));

			cell = createNewCell(row);
			cell.setCellValue(fc.getDealStatus());
			cell.setCellStyle(ReportExcelUtil.getDefaultCellStyle(wb,ReportExcelUtil.BG_COLOR_WHITE,ReportExcelUtil.getDefaultFont(wb), ReportExcelUtil.ALIGN_LEFT ,false,false));
			
			cell = createNewCell(row);
			cell.setCellValue(fc.getDealType());
			cell.setCellStyle(ReportExcelUtil.getDefaultCellStyle(wb,ReportExcelUtil.BG_COLOR_WHITE,ReportExcelUtil.getDefaultFont(wb), ReportExcelUtil.ALIGN_LEFT ,false,false));

		}
   }
}
